package com.hsnn.medstgmini.base.std.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.std.dao.CompProfileDao;
import com.hsnn.medstgmini.base.std.model.HospLevel;
import com.hsnn.medstgmini.base.std.model.StdCompinfo;
import com.hsnn.medstgmini.base.std.model.StdCompprofile;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.sys.model.SysUser;
import com.hsnn.medstgmini.util.AreaUtil;
import com.hsnn.medstgmini.util.Pagination;

/**
 * @category 企业端基础库资质管理
 * @author 蔡春龙
 * @date 2015年9月9日
 */
@Repository
public class CompProfileDaoImpl implements CompProfileDao {

	@Resource(name = "sessionFactory")
	private SessionFactory factory;

	@Autowired
	private HibernateSqlUtil sqlUtil;
	
	@Autowired
	private HibernateSqlUtil dao;

	/**
	 * @category 查看企业资质列表
	 * @date 2015年9月9日
	 * @param page
	 * @param compProfile
	 * @return
	 */
	public Pagination getSelfCompProfileList(Pagination page,
			StdCompprofile compProfile) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT  a.compprofile_id compprofileId,  a.comp_id compId,  a.profile_code profileCode,  a.profile_name profileName,  a.profile_type profileType,  DATE_FORMAT(a.profile_starttime, '%Y-%m-%d') profileStarttime,  DATE_FORMAT(a.profile_endtime, '%Y-%m-%d') profileEndtime,  a.profile_pic_addr profilePicAddr,  a.profile_pic_shortcut_addr profilePicShortcutAddr,  a.status,  a.remarks,  a.audit_status auditStatus,  DATE_FORMAT(a.audit_datetime, '%Y-%m-%d') auditDatetime,  a.audit_remark auditRemark,  c.name auditUser,  b.first_level_indi_code firstLevelIndiCode,  b.first_level_indi_name firstLevelIndiName FROM std_compprofile a LEFT JOIN dic_ent_profile b ON a.profile_type = b.dic_ent_profile_id LEFT JOIN sys_user c ON a.audit_user = c.id WHERE a.comp_id = :compId ");

		// 查询条件
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", compProfile.getCompId());

		if (compProfile.getAuditStatus() != null) {
			sb.append(" AND a.audit_status = :auditStatus ");
			params.put("auditStatus", compProfile.getAuditStatus());
		}

		if (compProfile.getStatus() != null) {
			sb.append(" AND a.status = :status ");
			params.put("status", compProfile.getStatus());
		}
		
		if (StringUtils.isNotBlank(compProfile.getProfileCode())) {
			sb.append(" AND a.profile_code = :profileCode ");
			params.put("profileCode", compProfile.getProfileCode());
		}

		if (StringUtils.isNotBlank(compProfile.getProfileName())) {
			sb.append(" AND a.profile_name LIKE :profileName ");
			params.put("profileName", "%" + compProfile.getProfileName() + "%");
		}

		sb.append(" ORDER BY a.comp_id DESC, a.compprofile_id ASC ");

		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		return page;
	}

	/**
	 * @category 查询企业资质类型列表(id, name)
	 * @date 2015年9月9日
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, String>> getCompProfileTypeList() {
		Session session = factory.getCurrentSession();
		String sql = "SELECT a.dic_ent_profile_id id, a.first_level_indi_name name FROM dic_ent_profile a WHERE a.status = 1";
		SQLQuery query = session.createSQLQuery(sql);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}

	/**
	 * @category 提交企业资质
	 * @date 2015年9月9日
	 * @param compProfile
	 */
	public void submitCompProfile(StdCompprofile compProfile) {
		Session session = factory.getCurrentSession();
		String sql = "UPDATE std_compprofile SET audit_status = 0, upd_user = :updUser, upd_datetime = SYSDATE() WHERE compprofile_id = :compprofileId";
		SQLQuery query = session.createSQLQuery(sql);
		query.setString("updUser", compProfile.getUpdUser());
		query.setString("compprofileId", compProfile.getCompprofileId());
		query.executeUpdate();
		session.flush();

		writeCompProfileLog(compProfile.getCompprofileId());
	}

	/**
	 * @category 保存企业资质日志
	 * @date 2015年9月9日
	 * @param ids
	 */
	public void writeCompProfileLog(String... ids) {
		Session session = factory.getCurrentSession();
		// ----begin	
        StringBuffer detail = new StringBuffer();
		StringBuffer sb = new StringBuffer()
		.append("  select profile_code,profile_name,profile_type,profile_pic_addr,profile_pic_shortcut_addr,remarks,status  ")
		.append(" from std_compprofile where compprofile_id =  :compprofileId ")
		.append(" union all ")
		.append(" select t.* from (select profile_code,profile_name,profile_type,profile_pic_addr,profile_pic_shortcut_addr,remarks,status ")
		.append("  from std_compprofile_log where compprofile_id = :compprofileId  order by upd_datetime desc limit 1 ) t");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("compprofileId", ids[0]);
		List<?> list = sqlUtil.getSqlList(sb.toString(), params);
		
		String[] colunm ={"资质编号","资质名称","资质图片","资质缩略图","备注","","状态"};
		if(list.size()==1){
			Object[] obj =(Object[]) list.get(0);
			obj[6] = obj[6]==null?"":"1".equals(obj[6].toString())?"启用":"停用";
			for(int i = 0;i<obj.length;i++){
				detail.append(";").append(colunm[i]).append(":").append(obj[i]==null?"":obj[i].toString());
			}
			detail.delete(0, 1);
		}else{
			Object[] obj1 =(Object[]) list.get(0);//修改后
			Object[] obj2 =(Object[]) list.get(1);//修改前
			obj1[6] = obj1[6]==null?"":"1".equals(obj1[6].toString())?"启用":"停用";
			obj2[6] = obj2[6]==null?"":"1".equals(obj2[6].toString())?"启用":"停用";
			for(int i = 0;i<obj1.length;i++){
				if(!(obj1[i]==null?"":obj1[i].toString()).equals(obj2[i]==null?"":obj2[i].toString())){
					detail.append(";").append(colunm[i]).append(":").append(obj2[i]==null?"":obj2[i].toString()).append(",").append(obj1[i]==null?"":obj1[i].toString());
				}
				
			}
			detail.delete(0, 1);
		}
		// ----end
		StringBuffer sql = new StringBuffer();
		sql.append(" insert into std_compprofile_log(log_id,compprofile_id,comp_id,profile_code,profile_name,profile_type,profile_starttime,profile_endtime,profile_pic_addr,profile_pic_shortcut_addr,status, ")
			.append(" remarks,upd_datetime,upd_user,audit_status,audit_datetime,audit_remark,audit_user,detail ")
			.append(" ) select ")
		    .append(" UUID(),t.compprofile_id,t.comp_id,t.profile_code,t.profile_name,t.profile_type,t.profile_starttime,t.profile_endtime,t.profile_pic_addr,t.profile_pic_shortcut_addr,t.status,t.remarks,t.upd_datetime,t.upd_user, ")
		    .append(" t.audit_status,t.audit_datetime,t.audit_remark,t.audit_user,:detail from std_compprofile t where t.compprofile_id= :compprofileId ");
		Query query=session.createSQLQuery(sql.toString());
		query.setString("compprofileId",ids[0]);
		query.setString("detail",detail.toString());
		query.executeUpdate();
		session.flush();
	}

	/**
	 * @category 查询有资质需要审核的企业列表
	 * @date 2015年9月9日
	 * @param page
	 * @return
	 */
	public Pagination getSomeCompinfoViewList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT A.comp_id compId, A.comp_code compCode, A.comp_name compName, A.org_code orgCode, A.comp_spel_code compSpelCode, A.comp_type compType, A.area_id areaId, A.comp_reg_id compRegId, A.status status, A.upd_datetime updDatetime, B.ar_fullname arFullname, C.comp_reg_name compRegName FROM std_compinfo A LEFT JOIN dic_area B on A.area_id = B.area_id LEFT JOIN dic_comptype C on C.comp_reg_id = A.comp_reg_id where 1 = 1 AND EXISTS ( SELECT * FROM std_compprofile d WHERE a.comp_id = d.comp_id AND d.audit_status = 0 ) ");

		// 查询条件
		Map<String, Object> params = new HashMap<String, Object>();
		StdCompinfo compinfo = (StdCompinfo) page.getForm();
		if (compinfo != null) {
			Short compType = compinfo.getCompType();
			if (compType != null) {
				sb.append(" and A.comp_type = :compType");
				params.put("compType", compType);
			}

			String areaId = compinfo.getAreaId();
			if (StringUtils.isNotBlank(areaId)) {
				sb.append(" and B.area_id LIKE :areaId");
				params.put("areaId", AreaUtil.escapeAreaId(areaId));
			}

			String compRegId = compinfo.getCompRegId();
			if (StringUtils.isNotBlank(compRegId)) {
				sb.append(" and A.comp_reg_id = :compRegId");
				params.put("compRegId", compRegId);
			}

			String compName = compinfo.getCompName();
			if (StringUtils.isNotBlank(compName)) {
				sb.append(" AND (A.comp_name LIKE :compName  OR A.comp_spel_code LIKE :compName) ");
				params.put("compName", "%" + compName.trim() + "%");
			}

			String compCode = compinfo.getCompCode();
			if (StringUtils.isNotBlank(compCode)) {
				sb.append(" and A.comp_code = :compCode");
				params.put("compCode", compCode.trim());
			}
			//状态
			String status = null;
			if (compinfo.getStatus() != null ) {
				 status = compinfo.getStatus()+"";
			}
			if (StringUtils.isNotBlank(status)) {
				sb.append(" AND A.status = :status ");
				params.put("status", status);
			}
		}
		sb.append(" order by A.comp_code desc " );
		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		return page;
	}

	/**
	 * @category 审核企业资质
	 * @date 2015年9月9日
	 * @param compProfile
	 * @param user
	 */
	public void auditCompProfile(StdCompprofile compProfile, SysUser user) {
		Session session = factory.getCurrentSession();
		String sql = "UPDATE std_compprofile SET status = :status, audit_status = :auditStatus, audit_remark = :auditRemark, audit_datetime = SYSDATE(), audit_user = :updUser, upd_user = :updUser, upd_datetime = SYSDATE() WHERE compprofile_id = :compprofileId";
		SQLQuery query = session.createSQLQuery(sql);
		query.setInteger("status", compProfile.getStatus());
		query.setInteger("auditStatus", compProfile.getAuditStatus());
		query.setString("auditRemark", compProfile.getAuditRemark());
		query.setString("updUser", user.getId());
		query.setString("compprofileId", compProfile.getCompprofileId());
		query.executeUpdate();
		session.flush();

		writeCompProfileLog(compProfile.getCompprofileId());
	}

	/**
	 * @category 变更企业资质状态
	 * @date 2015年9月10日
	 * @param compProfile
	 * @param user
	 */
	public void changeCompProfileStatus(StdCompprofile compProfile, SysUser user) {
		Session session = factory.getCurrentSession();
		String sql = "UPDATE std_compprofile SET status = :status, upd_user = :updUser, upd_datetime = SYSDATE() WHERE compprofile_id = :compprofileId";
		SQLQuery query = session.createSQLQuery(sql);
		query.setInteger("status", compProfile.getStatus());
		query.setString("updUser", user.getId());
		query.setString("compprofileId", compProfile.getCompprofileId());
		query.executeUpdate();
		session.flush();

		writeCompProfileLog(compProfile.getCompprofileId());
	}
	
	/**
	 * @category 查询维护记录详情
	 * @date 2015年9月28日
	 * @param logId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getCompProfileLogDetail(String logId) {
		String sql = " SELECT a.log_id logId,a.status,a.compprofile_id compprofileId, a.comp_id compId, a.profile_code profileCode, a.profile_name profileName, a.profile_type profileType, c.first_level_indi_name firstLevelIndiName, DATE_FORMAT( a.profile_starttime, '%Y-%m-%d' ) profileStarttime, DATE_FORMAT( a.profile_endtime, '%Y-%m-%d' ) profileEndtime, a.profile_pic_addr profilePicAddr, a.profile_pic_shortcut_addr profilePicShortcutAddr, a.remarks remark, DATE_FORMAT( a.upd_datetime, '%Y-%m-%d %T' ) updDatetime, d. NAME updUser FROM std_compprofile_log a JOIN ( SELECT a.compprofile_id, a.upd_datetime FROM std_compprofile_log a WHERE a.log_id = :logId ) b ON a.compprofile_id = b.compprofile_id AND a.upd_datetime <= b.upd_datetime LEFT JOIN dic_ent_profile c ON a.profile_type = c.dic_ent_profile_id LEFT JOIN sys_user d ON a.upd_user = d.id ORDER BY a.upd_datetime DESC limit 2 ";
		Map<String,String> params = new HashMap<String,String>();
		params.put("logId", logId);
		List<Map<String,Object>> list = (List<Map<String,Object>>) dao.getSqlMapList(sql.toString(), params);
		
		return list;
	}

}
